IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[csp_rptDepartmentalMetric_PendingSubmissionDetails]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[csp_rptDepartmentalMetric_PendingSubmissionDetails]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE  PROCEDURE [dbo].[csp_rptDepartmentalMetric_PendingSubmissionDetails]   
(      
   @PeriodType VARCHAR(50)      
, @PeriodStartDate SMALLDATETIME      
 ,@Institution varchar (50) = NULL
, @Department INT = NULL    
, @Unit VARCHAR(250)  = NULL
, @PrincipalInvestigator VARCHAR(50)=NULL    
, @Sponsor  VARCHAR(150) = NULL    
, @FundNumber VARCHAR(50) = NULL
, @DomainUserId INT =  NULL
)      
AS    
 
BEGIN 
	--INSIGHT_RPT_DB
	SELECT	ai.Institution, 
			ps.ProposalNumber, 
			ai.PrincipalInvestigator  AS PIName, 
			ai.Department,
			ps.ChiefCode,
			ai.SponsorName AS Sponsor,
			ps.ProposalStatus,
			ps.stat_date,
			ps.LastUpdatedBy,
			ps.DateReceived,
			ps.ProjectStartDate,
			ps.ProjectEndDate,
			ps.DeadlineDate,
			ps.PreawardManager,
			ps.SubmissionDate,
			ps.BudgetAmount,
			ps.InstrumentType
	FROM  rptMetric_PendingSubmissions ps
	INNER JOIN INSIGHT_RPT_DB.dbo.fnGetSecurityAgreements(@DomainUserId) sec
		ON (sec.FolderNumber = ps.ProposalNumber)
	INNER JOIN INSIGHT_RPT_DB.dbo.AgrAgreement_Info ai
		ON ai.FolderNumber = ps.ProposalNumber	
	WHERE
			(ai.institution= @Institution  OR @Institution IS NULL OR @Institution = 'All')
			AND (ai.DepartmentId = @Department OR @Department IS NULL OR @Department='0' )
			AND (ai.UnitId in (SELECT * FROM dbo.fnSplitFundNumbers(@Unit)) OR @Unit IS NULL OR @Unit=0 OR @Unit='') 
			AND  ps.PeriodStartDate = @PeriodStartDate   
			AND (ai.PrincipalInvestigatorId = @PrincipalInvestigator OR @PrincipalInvestigator IS NULL OR @PrincipalInvestigator=''OR @PrincipalInvestigator='0')    
			AND  (ai.Sponsorid = @Sponsor OR @Sponsor IS NULL OR @Sponsor='' OR @Sponsor='0')
			AND (ai.ProjectFundNumber IN (SELECT * FROM dbo.fnSplitFundNumbers(@FundNumber)) OR @FundNumber IS NULL OR @FundNumber='')
			AND ps.PeriodType=@PeriodType  
 
END

GO

